#!/usr/bin/env python3
import pandas as pd
import hashlib
import time
from datetime import datetime

def generate_order_no():
    """生成订单号"""
    prefix = 'ORD'
    date = datetime.now().strftime('%Y%m%d')
    import random
    import string
    random_str = ''.join(random.choices(string.ascii_uppercase + string.digits, k=6))
    return f"{prefix}{date}{random_str}"

try:
    # 读取Excel文件
    df = pd.read_excel('提取表格_2025年7.8号青岛豪而美工贸有限公司.xlsx', header=None)
    
    print("Excel文件简化分析结果：")
    print("=" * 60)
    print(f"数据形状: {df.shape}")
    print(f"总行数: {len(df)}")
    print(f"总列数: {len(df.columns)}")
    print()
    
    # 过滤空行
    valid_data = []
    for i in range(len(df)):
        row = df.iloc[i].tolist()
        if any(cell is not None and str(cell).strip() != '' for cell in row):
            valid_data.append(row)
    
    print(f"有效数据行数: {len(valid_data)}")
    print()
    
    # 简化的产品数据提取
    products = []
    
    for i in range(3, len(valid_data)):
        row = valid_data[i]
        
        # 只提取有产品名称和数量的行
        if (row[1] is not None and str(row[1]).strip() != '' and 
            row[4] is not None and str(row[4]).replace('.', '').isdigit()):
            
            product_name = str(row[1]).strip()
            color = str(row[2]).strip() if row[2] is not None and str(row[2]).strip() != '' else ''
            spec = str(row[3]).strip() if row[3] is not None and str(row[3]).strip() != '' else ''
            quantity = int(float(row[4]))
            
            if quantity > 0:
                # 生成型号名称
                model_name = product_name
                if color and color != 'nan':
                    model_name += f"-{color}"
                if spec and spec != 'nan':
                    model_name += f"-{spec}"
                
                product_info = {
                    'product_name': product_name,
                    'color': color if color != 'nan' else '',
                    'spec': spec if spec != 'nan' else '',
                    'model_name': model_name,
                    'quantity': quantity
                }
                
                products.append(product_info)
                
                print(f"产品: {product_name}")
                print(f"  颜色: {color if color != 'nan' else '无'}")
                print(f"  规格: {spec if spec != 'nan' else '无'}")
                print(f"  型号: {model_name}")
                print(f"  数量: {quantity}")
                print()
    
    print("=" * 60)
    print("简化导入方案：")
    print("=" * 60)
    
    # 按产品名称分组统计
    product_groups = {}
    for product in products:
        model_name = product['model_name']
        if model_name not in product_groups:
            product_groups[model_name] = {
                'product_name': product['product_name'],
                'color': product['color'],
                'spec': product['spec'],
                'model_name': model_name,
                'total_quantity': 0
            }
        product_groups[model_name]['total_quantity'] += product['quantity']
    
    print(f"总共 {len(product_groups)} 个唯一产品型号：")
    print()
    
    total_quantity = 0
    for model_name, info in product_groups.items():
        print(f"型号: {info['model_name']}")
        print(f"  产品: {info['product_name']}")
        print(f"  颜色: {info['color'] if info['color'] else '无'}")
        print(f"  规格: {info['spec'] if info['spec'] else '无'}")
        print(f"  总数量: {info['total_quantity']}")
        print()
        total_quantity += info['total_quantity']
    
    print(f"总计: {total_quantity} 件产品")
    print()
    
    # 简化的订单创建方案
    print("简化订单创建方案：")
    print("=" * 60)
    print("方案1: 创建一个订单，包含所有产品")
    print("方案2: 按序号分组创建多个订单")
    print("方案3: 按产品类型分组创建订单")
    print()
    
    # 方案1：单个订单
    print("方案1 - 单个订单示例：")
    order1 = {
        'order_no': generate_order_no(),
        'customer_name': '青岛豪而美工贸有限公司',
        'total_quantity': total_quantity,
        'models': product_groups
    }
    print(f"订单号: {order1['order_no']}")
    print(f"客户: {order1['customer_name']}")
    print(f"总数量: {order1['total_quantity']}")
    print(f"产品型号数: {len(order1['models'])}")
    print()
    
    # 方案2：按序号分组
    print("方案2 - 按序号分组（原始Excel结构）：")
    orders_by_sequence = []
    current_order = None
    
    for i in range(3, len(valid_data)):
        row = valid_data[i]
        
        # 检查是否有序号（新订单）
        if row[0] is not None and str(row[0]).strip() != '' and str(row[0]).replace('.', '').isdigit():
            if current_order and current_order['models']:
                orders_by_sequence.append(current_order)
            
            current_order = {
                'order_no': generate_order_no(),
                'customer_name': '青岛豪而美工贸有限公司',
                'total_quantity': 0,
                'models': {}
            }
        
        # 提取产品信息
        if (row[1] is not None and str(row[1]).strip() != '' and 
            row[4] is not None and str(row[4]).replace('.', '').isdigit()):
            
            product_name = str(row[1]).strip()
            color = str(row[2]).strip() if row[2] is not None and str(row[2]).strip() != '' else ''
            spec = str(row[3]).strip() if row[3] is not None and str(row[3]).strip() != '' else ''
            quantity = int(float(row[4]))
            
            if quantity > 0 and current_order:
                model_name = product_name
                if color and color != 'nan':
                    model_name += f"-{color}"
                if spec and spec != 'nan':
                    model_name += f"-{spec}"
                
                if model_name not in current_order['models']:
                    current_order['models'][model_name] = 0
                current_order['models'][model_name] += quantity
                current_order['total_quantity'] += quantity
    
    if current_order and current_order['models']:
        orders_by_sequence.append(current_order)
    
    print(f"按序号分组创建了 {len(orders_by_sequence)} 个订单：")
    for i, order in enumerate(orders_by_sequence):
        print(f"  订单 {i+1}: {order['order_no']} - {order['total_quantity']}件 - {len(order['models'])}个型号")
    
    print()
    print("推荐使用方案2，保持原始Excel的订单结构！")
    
except Exception as e:
    print(f"错误: {e}")
    import traceback
    traceback.print_exc()
